package com.topica.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.topica.object.AnswerItem;
import com.topica.object.CourseItem;
import com.topica.object.LectureItem;
import com.topica.object.QuestionItem;
import com.topica.object.QuizItem;
import com.topica.object.QuizLogItem;
import com.topica.object.WeekItem;

public class DataBaseHelper extends SQLiteOpenHelper {
	private static final String TABLE_COURSE = "course";
	private static final String TABLE_WEEK = "week";
	private static final String TABLE_LECTURE = "lecture";
	private static final String TABLE_ANSWER = "answer";
	private static final String TABLE_QUIZ = "quiz";
	private static final String TABLE_QUESTION = "question";
	private static final String TABLE_QUIZ_LOG = "quiz_log";
	private static final int DATABASE_VERSION = 2;
	private static String DB_NAME = "topica.db";
	private SQLiteDatabase myDataBase;

	/**
	 * Constructor Takes and keeps a reference of the passed context in order to
	 * access to the application assets and resources.
	 * 
	 * @param context
	 */
	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, DATABASE_VERSION);
		myDataBase = getWritableDatabase();
	}

	@Override
	public synchronized void close() {
		if (myDataBase != null)
			myDataBase.close();
		super.close();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE \"answer\" (\"id\" INTEGER,\"question_id\" INTEGER,\"fraction\" TEXT,\"answer\" TEXT)");
		db.execSQL("CREATE TABLE course (\"course_id\" INTEGER,\"name\" TEXT,\"info\" TEXT,\"link\" TEXT)");
		db.execSQL("CREATE TABLE lecture (\"lecture_id\" INTEGER,\"week_id\" INTEGER,\"name\" TEXT,\"path_book\" TEXT,\"path_video\" TEXT,\"path_audio\" TEXT,\"id\" INTEGER)");
		db.execSQL("CREATE TABLE \"question\" (\"question_id\" INTEGER,\"name\" TEXT,\"question_text\" TEXT,\"question_image\" BLOB)");
		db.execSQL("CREATE TABLE \"quiz\" (\"row_id\"integer PRIMARY KEY, \"id\" integer,\"quiz_id\" integer,\"quest_ids\" TEXT,\"num_question\" integer,\"time\" TEXT,\"name\" TEXT,\"time_open\" TEXT,\"time_close\" TEXT)");
		db.execSQL("CREATE TABLE \"quiz_log\" (\"id\" INTEGER,\"quiz_id\" INTEGER,\"user_id\" INTEGER,\"user_choice\" TEXT,\"start_time\" TEXT,\"end_time\" TEXT,\"mark\" REAL)");
		db.execSQL("CREATE TABLE \"week\" (\"week_id\" INTEGER,\"course_id\" INTEGER,\"name\" TEXT,\"practice\" TEXT,\"quiz_id\" INTEGER, \"number_practice\" INTEGER DEFAULT 3,\"start_week\" TEXT, \"end_week\" TEXT)");
	}

	public void insertQuizLog(QuizLogItem item) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("id", item.getId());
		values.put("quiz_id", item.getQuizId());
		values.put("user_id", item.getUser_id());
		values.put("user_choice", item.getUser_choice());
		values.put("start_time", item.getStart_time());
		values.put("end_time", item.getEnd_time());
		values.put("mark", item.getMark());
		db.insert("quiz_log", null, values);
		db.close();
	}

	public List<QuizLogItem> getQuizLog(int userId, int quizId) {
		String query = "SELECT mark FROM quiz_log WHERE user_id=" + userId
				+ " AND quiz_id=" + quizId;
		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);
		List<QuizLogItem> list = new ArrayList<QuizLogItem>();
		if (cursor.moveToFirst()) {
			do {
				QuizLogItem item = new QuizLogItem();
				item.setMark(cursor.getString(0));
				list.add(item);
			} while (cursor.moveToNext());
		}
		db.close();
		return list;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("ALTER TABLE quiz_log ADD COLUMN \"status\" BOOL DEFAULT false");
	}

	public void insertDataToDB(ArrayList<String> data) throws SQLiteException {
		for (String sql : data) {
			myDataBase.execSQL(sql);
		}
	}

	public ArrayList<CourseItem> getListCourse() {
		Cursor cursor = myDataBase.query(TABLE_COURSE, new String[] {
				"course_id", "name", "info", "link" }, null, null, null, null,
				null);
		ArrayList<CourseItem> rs = new ArrayList<CourseItem>();
		try {
			cursor.moveToFirst();
			String name, info, link;
			int id;
			while (!cursor.isAfterLast()) {
				id = cursor.getInt(0);
				name = cursor.getString(1);
				info = cursor.getString(2);
				link = cursor.getString(3);
				rs.add(new CourseItem(id, name, info, link));
				cursor.moveToNext();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			cursor.close();
		}
		return rs;
	}

	public List<WeekItem> getAllWeekTime(String courseId) {
		Cursor cursor = myDataBase.query(TABLE_WEEK, new String[] {
				"start_week", "end_week" }, "course_id = ?",
				new String[] { courseId }, null, null, null);
		List<WeekItem> list = new ArrayList<WeekItem>();
		cursor.moveToFirst();
		String startWeek;
		String endWeek;
		while (!cursor.isAfterLast()) {
			startWeek = cursor.getString(0);
			endWeek = cursor.getString(1);
			WeekItem item = new WeekItem();
			item.setStartWeek(startWeek);
			item.setEndWeek(endWeek);
			list.add(item);
			cursor.moveToNext();
		}
		myDataBase.close();
		return list;
	}

	public ArrayList<WeekItem> getListWeek(String courseId) {
		Cursor cursor = myDataBase.query(TABLE_WEEK, new String[] { "week_id",
				"course_id", "quiz_id", "name", "practice", "start_week",
				"end_week" }, "course_id = ?", new String[] { courseId }, null,
				null, null);
		ArrayList<WeekItem> rs = new ArrayList<WeekItem>();
		try {
			cursor.moveToFirst();
			int weekId, course_id, quiz_id;
			String name, practice, startWeek, endWeek;
			while (!cursor.isAfterLast()) {
				weekId = cursor.getInt(0);
				course_id = cursor.getInt(1);
				quiz_id = cursor.getInt(2);
				name = cursor.getString(3);
				practice = cursor.getString(4);
				startWeek = cursor.getString(5);
				endWeek = cursor.getString(6);
				rs.add(new WeekItem(weekId, course_id, quiz_id, name, practice,
						startWeek, endWeek));
				cursor.moveToNext();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			cursor.close();
		}
		return rs;
	}

	public ArrayList<LectureItem> getListLectures(String weekId) {
		Cursor cursor = myDataBase.query(TABLE_LECTURE, new String[] {
				"lecture_id", "week_id", "name", "path_book", "path_video",
				"path_audio" }, "week_id = ?", new String[] { weekId }, null,
				null, null);
		ArrayList<LectureItem> rs = new ArrayList<LectureItem>();
		try {
			cursor.moveToFirst();
			int lecture_id, week_id;
			String name, path_book, path_video, path_audio;
			while (!cursor.isAfterLast()) {
				lecture_id = cursor.getInt(0);
				week_id = cursor.getInt(1);
				name = cursor.getString(2);
				path_book = cursor.getString(3);
				path_video = cursor.getString(4);
				path_audio = cursor.getString(5);
				rs.add(new LectureItem(lecture_id, week_id, name, path_book,
						path_video, path_audio));
				cursor.moveToNext();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			cursor.close();
		}
		return rs;
	}

	public ArrayList<AnswerItem> getListAnswers(String questionId) {
		Cursor cursor = myDataBase.query(TABLE_ANSWER, new String[] { "id",
				"fraction", "answer" }, "question_id = ?",
				new String[] { questionId }, null, null, null);
		ArrayList<AnswerItem> rs = new ArrayList<AnswerItem>();
		try {
			cursor.moveToFirst();
			int id_answer;
			int fraction;
			String answer;

			while (!cursor.isAfterLast()) {
				id_answer = cursor.getInt(0);
				fraction = cursor.getInt(1);
				answer = cursor.getString(2);
				rs.add(new AnswerItem(id_answer, fraction, answer));
				cursor.moveToNext();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			cursor.close();
		}
		return rs;
	}

	public ArrayList<Integer> getListQuiz(String quizId) {
		Cursor cursor = myDataBase.query(TABLE_QUIZ, new String[] { "id" },
				"quiz_id = ?", new String[] { quizId }, null, null, null);
		ArrayList<Integer> rs = new ArrayList<Integer>();
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				rs.add(cursor.getInt(0));
				cursor.moveToNext();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			cursor.close();
		}
		return rs;
	}

	public QuizItem getQuizItem(String rowId, String quizId) {
		QuizItem item = null;
		Cursor cursor = myDataBase.query(TABLE_QUIZ, new String[] { "id",
				"quiz_id", "quest_ids", "num_question", "time", "name",
				"time_open", "time_close" }, "quiz_id = ? AND id = ? ",
				new String[] { quizId, rowId }, null, null, null);
		cursor.moveToFirst();

		int id = cursor.getInt(0);
		int quiz_id = cursor.getInt(1);
		String quest_ids = cursor.getString(2);
		int num_question = cursor.getInt(3);
		int time = cursor.getInt(4);
		String name = cursor.getString(5);
		String time_open = cursor.getString(6);
		String time_close = cursor.getString(7);
		item = new QuizItem(id, quiz_id, quest_ids, num_question, time, name,
				time_open, time_close);
		cursor.close();
		return item;
	}

	public QuestionItem getListQuestion(String questionId) {
		Log.e("DataBase", "questionId =  " + questionId);
		try {
			Cursor cursor = myDataBase.query(TABLE_QUESTION, new String[] {
					"question_id", "name", "question_text" },
					"question_id = ?", new String[] { questionId }, null, null,
					null);
			int question_id;
			String name, question_text;

			cursor.moveToFirst();
			question_id = cursor.getInt(0);
			name = cursor.getString(1);
			question_text = cursor.getString(2);
			QuestionItem item = new QuestionItem(question_id, name,
					question_text);
			cursor.close();
			return item;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public String getLinkDownloadCourse(String courseId) {
		Cursor cursor = myDataBase.query(TABLE_COURSE, new String[] { "link" },
				"course_id = ?", new String[] { courseId }, null, null, null);
		String link;
		try {
			cursor.moveToFirst();
			link = cursor.getString(0);
			return link;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			cursor.close();
		}
		return "";
	}

	public int getPracticeTime(String weekId) {
		Cursor cursor = myDataBase.query(TABLE_WEEK,
				new String[] { "number_practice" }, "week_id = ?",
				new String[] { weekId }, null, null, null);
		int time;
		try {
			cursor.moveToFirst();
			time = cursor.getInt(0);
			return time;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			cursor.close();
		}
		return -1;
	}

	public void updatePracticeTime(String weekId, int number) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("number_practice", number);
		String whereClause = "week_id" + "=" + weekId;
		db.update(TABLE_WEEK, values, whereClause, null);
		db.close();
	}

	public void updateQuizLog(int id, int quizId, int userId) {
		ContentValues values = new ContentValues();
		values.put("status", true);
		myDataBase.update(TABLE_QUIZ_LOG, values,
				"id = ? AND quiz_id = ? AND user_id = ? ", new String[] {
						"" + id, "" + quizId, "" + userId });
	}

	public ArrayList<QuizLogItem> getQuizLog() {
		Cursor cursor = myDataBase.query(TABLE_QUIZ_LOG, new String[] { "id",
				"quiz_id", "user_id", "user_choice", "start_time", "end_time",
				"mark" }, "status = ?", new String[] { "false" }, null, null,
				null);
		ArrayList<QuizLogItem> rs = new ArrayList<QuizLogItem>();
		int id = 0, quiz_id = 0, user_id = 0;
		String start_time = "", end_time = "";
		String mark = "";
		String user_choice = "";
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				id = cursor.getInt(0);
				quiz_id = cursor.getInt(1);
				user_id = cursor.getInt(2);
				user_choice = cursor.getString(3);
				start_time = cursor.getString(4);
				end_time = cursor.getString(5);
				mark = "" + cursor.getFloat(6);
				QuizLogItem item = new QuizLogItem(id, quiz_id, user_id,
						user_choice, start_time, end_time, mark);
				rs.add(item);
				cursor.moveToNext();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			cursor.close();
		}
		return rs;
	}
}
